
package ImportHelpers;

use strict;
use warnings;
use utf8;
use DBI;
use open ':encoding(utf8)';
use vars qw($VERSION @ISA @EXPORT @EXPORT_OK);
use Unicode::Normalize qw(decompose NFC);

require Exporter;

@ISA = qw(Exporter AutoLoader);
# Items to export into callers namespace by default. Note: do not export
# names by default without a very good reason. Use EXPORT_OK instead.
# Do not simply export all your public functions/methods/constants.
@EXPORT = qw();
$VERSION = '0.01';


#OSIS codes
our @osisOldTestamentCodes = qw(Gen Exod Lev Num Deut Josh Judg Ruth 1Sam 2Sam 1Kgs 2Kgs 1Chr 2Chr Ezra Neh Esth Job Ps Prov Eccl Song Isa Jer Lam Ezek Dan Hos Joel Amos Obad Jonah Mic Nah Hab Zeph Hag Zech Mal );
our @osisNewTestamentCodes = qw(Matt Mark Luke John Acts Rom 1Cor 2Cor Gal Eph Phil Col 1Thess 2Thess 1Tim 2Tim Titus Phlm Heb Jas 1Pet 2Pet 1John 2John 3John Jude Rev );
our @osisDeuterocanonicalCodes = qw(Bar AddDan PrAzar Bel SgThree Sus 1Esd 2Esd AddEsth EpJer Jdt 1Macc 2Macc 3Macc 4Macc PrMan Sir Tob Sir Wis );
our %osisBookNames = (
	"Gen" => "Genesis",
	"Exod" => "Exodus",
	"Lev" => "Leviticus",
	"Num" => "Numbers",
	"Deut" => "Deuteronomy",
	"Josh" => "Joshua",
	"Judg" => "Judges",
	"Ruth" => "Ruth",
	"1Sam" => "1 Samuel",
	"2Sam" => "2 Samuel",
	"1Kgs" => "1 Kings",
	"2Kgs" => "2 Kings",
	"1Chr" => "1 Chronicles",
	"2Chr" => "2 Chronicles",
	"Ezra" => "Ezra",
	"Neh" => "Nehemiah",
	"Esth" => "Esther",
	"Job" => "Job",
	"Ps" => "Psalms",
	"Prov" => "Proverbs",
	"Eccl" => "Ecclesiastes",
	"Song" => "Song of Solomon",
	"Isa" => "Isaiah",
	"Jer" => "Jeremiah",
	"Lam" => "Lamentations",
	"Ezek" => "Ezekiel",
	"Dan" => "Daniel",
	"Hos" => "Hosea",
	"Joel" => "Joel",
	"Amos" => "Amos",
	"Obad" => "Obadiah",
	"Jonah" => "Jonah",
	"Mic" => "Micah",
	"Nah" => "Nahum",
	"Hab" => "Habakkuk",
	"Zeph" => "Zephaniah",
	"Hag" => "Haggai",
	"Zech" => "Zechariah",
	"Mal" => "Malachi",
	
	"Matt" => "Matthew",
	"Mark" => "Mark",
	"Luke" => "Luke",
	"John" => "John",
	"Acts" => "Acts",
	"Rom" => "Romans",
	"1Cor" => "1 Corinthians",
	"2Cor" => "2 Corinthians",
	"Gal" => "Galatians",
	"Eph" => "Ephesians",
	"Phil" => "Philippians",
	"Col" => "Colossians",
	"1Thess" => "1 Thessalonians",
	"2Thess" => "2 Thessalonians",
	"1Tim" => "1 Timothy",
	"2Tim" => "2 Timothy",
	"Titus" => "Titus",
	"Phlm" => "Philemon",
	"Heb" => "Hebrews",
	"Jas" => "James",
	"1Pet" => "1 Peter",
	"2Pet" => "2 Peter",
	"1John" => "1 John",
	"2John" => "2 John",
	"3John" => "3 John",
	"Jude" => "Jude",
	"Rev" => "Revelation",
	
	"Bar" => "Baruch",
	"AddDan" => "Additions to Daniel",
	"PrAzar" => "Prayer of Azariah",
	"Bel" => "Bel and the Dragon",
	"SgThree" => "Song of the Three Young Men",
	"Sus" => "Susanna",
	"1Esd" => "1 Esdras",
	"2Esd" => "2 Esdras",
	"AddEsth" => "Additions to Esther",
	"EpJer" => "Epistle of Jeremiah",
	"Jdt" => "Judith",
	"1Macc" => "1 Maccabees",
	"2Macc" => "2 Maccabees",
	"3Macc" => "3 Maccabees",
	"4Macc" => "4 Maccabees",
	"PrMan" => "Prayer of Manasseh",
	"Sir" => "Sirach/Ecclesiasticus",
	"Tob" => "Tobit",
	"Sir" => "Sirach/Ecclesiasticus",
	"Wis" => "Wisdom of Solomon"
);
our %unboundNumToOsisCode = (
	'01O' => "Gen",
	'02O' => "Exod",
	'03O' => "Lev",
	'04O' => "Num",
	'05O' => "Deut",
	'06O' => "Josh",
	'07O' => "Judg",
	'08O' => "Ruth",
	'09O' => "1Sam",
	'10O' => "2Sam",
	'11O' => "1Kgs",
	'12O' => "2Kgs",
	'13O' => "1Chr",
	'14O' => "2Chr",
	'15O' => "Ezra",
	'16O' => "Neh",
	'17O' => "Esth",
	'18O' => "Job",
	'19O' => "Ps",
	'20O' => "Prov",
	'21O' => "Eccl",
	'22O' => "Song",
	'23O' => "Isa",
	'24O' => "Jer",
	'25O' => "Lam",
	'26O' => "Ezek",
	'27O' => "Dan",
	'28O' => "Hos",
	'29O' => "Joel",
	'30O' => "Amos",
	'31O' => "Obad",
	'32O' => "Jonah",
	'33O' => "Mic",
	'34O' => "Nah",
	'35O' => "Hab",
	'36O' => "Zeph",
	'37O' => "Hag",
	'38O' => "Zech",
	'39O' => "Mal",
	
	'40N' => "Matt",
	'41N' => "Mark",
	'42N' => "Luke",
	'43N' => "John",
	'44N' => "Acts",
	'45N' => "Rom",
	'46N' => "1Cor",
	'47N' => "2Cor",
	'48N' => "Gal",
	'49N' => "Eph",
	'50N' => "Phil",
	'51N' => "Col",
	'52N' => "1Thess",
	'53N' => "2Thess",
	'54N' => "1Tim",
	'55N' => "2Tim",
	'56N' => "Titus",
	'57N' => "Phlm",
	'58N' => "Heb",
	'59N' => "Jas",
	'60N' => "1Pet",
	'61N' => "2Pet",
	'62N' => "1John",
	'63N' => "2John",
	'64N' => "3John",
	'65N' => "Jude",
	'66N' => "Rev",
	
	'67A' => "Tob",
	'68A' => "Jdt",
	'69A' => "AddEsth",
	'70A' => "Wis",
	'71A' => "Sir",
	'72A' => "Baruch",
	'73A' => "EpJer",
	'74A' => "PrAzar",
	'75A' => "Sus",
	'76A' => "Bel",
	'77A' => "1Macc",
	'78A' => "2Macc",
	'79A' => "3Macc",
	'80A' => "4Macc",
	'81A' => "1Esd",
	'82A' => "2Esd",
	'83A' => "PrMan",
	#'84A' => "Psalm 151"
	#'85A' => "Psalm of Solomon
	#'86A' => "Odes
);

#Database connection
my $dsn = 'DBI:mysql:open_scriptures:127.0.0.1';
my ($id, $password);
our $dbh = DBI->connect($dsn, "root", '');
$dbh->do('use `open_scriptures`;');


my $insertToken = $dbh->prepare('INSERT INTO token (data, type, position, work) VALUES(?,?,?,?)');
my $insertTokenParsing = $dbh->prepare('INSERT INTO token_parsing (token, parse, strongs, lemma, work) VALUES(?,?,?,?,?)');

#my $insertBookGroup = $dbh->prepare("INSERT INTO ref (type, start_token, numerical_start, name) VALUES ('bookGroup', ?, ?, INSERT_TEXT(NULL, 'en', ?, NULL))");
my $insertBook    = $dbh->prepare("INSERT INTO ref (type, work, osis_id, start_token, numerical_start, position, name) VALUES ('book', ?, ?, ?, ?, ?, INSERT_TEXT(NULL, 'en', ?, NULL))");
my $insertChapter = $dbh->prepare("INSERT INTO ref (type, work, osis_id, start_token, numerical_start, position, parent) VALUES ('chapter', ?, ?, ?, ?, ?, ?)");
my $insertVerse   = $dbh->prepare("INSERT INTO ref (type, work, osis_id, start_token, numerical_start, position, parent) VALUES ('verse', ?, ?, ?, ?, ?, ?)");



sub getSourceDirName {
	my $file = shift;
	$file =~ s/\.pl$//;
	if(!-d $file){
		die "'$file' is not a directory";
	}
	return $file;
}



sub getSourceExternalURL {
	my $sourceDir = shift;
	
	my $externalURL;
	
	open EXTERNALS, "$sourceDir/../externals.txt" or die "Could not find: $sourceDir/../externals.txt";
	while(<EXTERNALS>){
		chomp;
		next if /^\s*(#.+)?$/;
		my($dir, $url) = split(/\s+/, $_, 2);
		if($dir eq $sourceDir){
			$url =~ s{^\s+}{};
			$url =~ s{\s+$}{};
			$externalURL = $url;
			last;
		}
	}
	close EXTERNALS;
	
	return $externalURL or die "Could not find the source URL for $sourceDir";
}

my $selectWorkByOsisID = $dbh->prepare("SELECT id FROM work WHERE osis_id = ?");
sub removeExistingWork {
	my $osisID = shift;
	$selectWorkByOsisID->execute($osisID);
	if($selectWorkByOsisID){
		my $workHash = $selectWorkByOsisID->fetchrow_hashref;
		if($workHash){
			$dbh->do("DELETE FROM ref WHERE type = 'verse' AND work = " . $workHash->{id});
			$dbh->do("DELETE FROM ref WHERE type = 'chapter' AND work = " . $workHash->{id});
			$dbh->do("DELETE FROM ref WHERE type = 'book' AND work = " . $workHash->{id});
			$dbh->do("DELETE FROM work WHERE id = " . $workHash->{id}); #triggers remove all tokens and groups
		}
	}
}

sub removeAllWorks {
	$dbh->do("DELETE FROM ref WHERE type = 'verse'");
	$dbh->do("DELETE FROM ref WHERE type = 'chapter'");
	$dbh->do("DELETE FROM ref WHERE type = 'book'");
	$dbh->do("DELETE FROM work");
}

our $lastWorkID;
#our $tokenID;
#our $position = 0;

our %workContext = ();


my $selectBookStartToken = $dbh->prepare("SELECT token.* FROM token INNER JOIN ref ON token.id = ref.start_token AND ref.type = 'book' WHERE token.work = ? AND ref.osis_id = ?");
my $selectBookEndToken   = $dbh->prepare("SELECT token.* FROM token INNER JOIN ref ON token.id = ref.end_token   AND ref.type = 'book' WHERE token.work = ? AND ref.osis_id = ?");
my $selectBookTokens     = $dbh->prepare("SELECT token.* FROM token WHERE work = ? AND position >= ? AND position <= ? ORDER BY position");
sub executeBookTokensQuery {
	my %args = @_;
	
	$selectBookStartToken->execute($args{work}, $args{book});
	my $startToken = $selectBookStartToken->fetchrow_hashref;
	$selectBookEndToken->execute($args{work}, $args{book});
	my $endToken = $selectBookEndToken->fetchrow_hashref;
	
	return 0 if $selectBookTokens->execute($args{work}, $startToken->{position}, $endToken->{position}) == 0;
	return $selectBookTokens;
}

my $selectLicensyByURL = $dbh->prepare("SELECT * FROM license WHERE url = ?");
sub insertWork {
	my %args = @_;
	removeExistingWork($args{osis_id});
	
	$dbh->do("INSERT INTO work ( id, name, type, language, osis_id, copyright, url ) VALUES(
							 ?,
							 INSERT_TEXT(NULL, 'en', ?, NULL),
							 ?,
							 ?,
							 ?,
							 INSERT_TEXT(NULL, 'en', ?, NULL),
							 ?
							)", undef,
						(
							exists $args{id} ? $args{id} : undef,
							$args{name},
							$args{type},
							exists $args{language} ? $args{language} : undef,
							$args{osis_id},
							$args{copyright},
							$args{url}
						)
	);
	$lastWorkID = $args{id} ? $args{id} : $dbh->last_insert_id(undef, undef, undef, undef);
	
	#Assign licenses
	foreach my $licenseURL (@{$args{licenses}}){
		$selectLicensyByURL->execute($licenseURL);
		my $licenseID = $selectLicensyByURL->fetchrow_hashref->{id};
		$dbh->do("INSERT INTO work_license ( work, license ) VALUES(?, ?)", undef, (
			$lastWorkID, $licenseID
		));
	}
	
	$workContext{$lastWorkID} = {
		position => 0,
		tokenID  => 0,
		lastBook => 0,
		bookRefID => 0,
		lastChapter => 0,
		chapterRefID => 0,
		lastVerse => 0,
		verseRefID => 0,
		refPosition => 1
	};

	return $lastWorkID;
}

our @okBooks = qw(
	Jude
);
my %seenOkBooks; #1Pet 2Pet 1John 2John 3John 
sub isBookOK {
	return 1;
	
	return (shift eq 'Jude');
	
	my $book = shift;
	return $seenOkBooks{$book} if exists $seenOkBooks{$book};
	
	return $seenOkBooks{$book} = grep /^$book$/, @okBooks;
	
	#my @ok = qw(
	#	1John 2John 3John Jude
	#);
	#return 0 if !$book || $book ne '1John'; #($book ne '1John' && $book ne '2John' && $book ne '3John');
	#return 0 if !$book || ($book ne '1John' && $book ne '2John' && $book ne '3John');
	#return 0 if !grep /^$book$/, @ok;
	#return 0 if !$book || ($book ne '3John');
	#return 1;
}

sub insertToken {
	my %args = @_;
	die("Must provide data") if(!exists $args{data});
	my $workID = exists $args{work} ? $args{work} : $lastWorkID;
	
	if(exists $args{position}){
		$workContext{$workID}->{'position'} = $args{position};
	}
	else {
		++$workContext{$workID}->{position};
	}
	
	$insertToken->execute(
		NFC($args{data}),
		exists $args{type} ? $args{type} : undef,
		$workContext{$workID}->{'position'},
		$workID
	);
	$workContext{$workID}->{previousTokenID} = $workContext{$workID}->{tokenID} if exists $workContext{$workID}->{tokenID};
	$workContext{$workID}->{tokenID} = $dbh->last_insert_id(undef, undef, undef, undef);
	
	if(exists $args{parsing} || exists $args{lemma} || exists $args{strongs}){
		insertTokenParsing(%args);
	}
	
	if(exists $args{book} || exists $args{chapter} || exists $args{verse}){
		saveRef(%args);
	}
	
	return $workContext{$workID}->{tokenID};
}

my $tokenParsingID;
sub insertTokenParsing {
	my %args = @_;
	my $workID = exists $args{work}? $args{work} : $lastWorkID;
	my $tokenID = exists $args{token}? $args{token} : $workContext{$workID}->{tokenID};
	
	$insertTokenParsing->execute(
		$tokenID,
		exists $args{parsing} ? NFC $args{parsing} : undef,
		exists $args{strongs} ? NFC $args{strongs} : undef,
		exists $args{lemma}   ? NFC $args{lemma}   : undef,
		$workID
	);
	$workContext{$workID}->{tokenParsingID} = $dbh->last_insert_id(undef, undef, undef, undef);
	return $workContext{$workID}->{tokenParsingID};
}


#my($lastBook, $lastChapter, $lastVerse) = (-1, -1, -1);
#my($bookRefID, $chapterRefID, $verseRefID);
our $updateRefEndToken = $dbh->prepare("UPDATE ref SET end_token = ? WHERE id = ?");
#my $refPosition = 1;
sub saveRef {
	my %args = @_;
	my $workID = exists $args{work}? $args{work} : $lastWorkID;
	my $tokenID = exists $args{token}? $args{token} : $workContext{$workID}->{tokenID};
	
	my $actionTaken = 0;
	
	if(exists $args{book} && $args{book} ne $workContext{$workID}->{lastBook}){ #OSIS
		print " - $args{book}\n";
		$updateRefEndToken->execute($workContext{$workID}->{previousTokenID}, $workContext{$workID}->{bookRefID}) if $workContext{$workID}->{bookRefID};
		
		#$workContext{$workID}->{refPosition} = 1; #reset for each book
		$workContext{$workID}->{lastChapter} = 0; #reset for each book
		#work, osis_id, start_token, position, name
		$insertBook->execute(
			$workID,
			$args{book},
			$tokenID,
			(grep { $osisNewTestamentCodes[$_] eq $args{book} } 0..$#osisNewTestamentCodes)[0] + 1, #$args{book}, #index
			$workContext{$workID}->{refPosition},
			$osisBookNames{$args{book}}
		);
		$workContext{$workID}->{bookRefID} = $dbh->last_insert_id(undef, undef, undef, undef);
		$workContext{$workID}->{lastBook} = $args{book};
		$actionTaken++;
	}
	if(exists $args{chapter} && $args{chapter} ne $workContext{$workID}->{lastChapter}){
		$updateRefEndToken->execute($workContext{$workID}->{previousTokenID}, $workContext{$workID}->{chapterRefID}) if $workContext{$workID}->{chapterRefID};
		#work, osis_id, start_token, numerical_start, position, parent
		$insertChapter->execute(
			$workID,
			$args{book} . "." . int($args{chapter}),
			$tokenID,
			$args{chapter},
			$workContext{$workID}->{refPosition},
			$workContext{$workID}->{bookRefID}
		);
		$workContext{$workID}->{chapterRefID} = $dbh->last_insert_id(undef, undef, undef, undef);
		$workContext{$workID}->{lastChapter} = $args{chapter};
		$actionTaken++;
	}
	if(exists $args{verse} && $args{verse} ne $workContext{$workID}->{lastVerse}){
		$updateRefEndToken->execute($workContext{$workID}->{previousTokenID}, $workContext{$workID}->{verseRefID}) if $workContext{$workID}->{verseRefID};
		#work, osis_id, start_token, numerical_start, position, parent
		$insertVerse->execute(
			$workID,
			$args{book} . "." . int($args{chapter}) . "." . int($args{verse}),
			$tokenID,
			$args{verse},
			$workContext{$workID}->{refPosition},
			$workContext{$workID}->{chapterRefID}
		);
		$workContext{$workID}->{verseRefID} = $dbh->last_insert_id(undef, undef, undef, undef);
		$workContext{$workID}->{lastVerse} = $args{verse};
		$actionTaken++;
	}
	
	$workContext{$workID}->{refPosition}++ if $actionTaken;
}

sub finishBookRefs {
	my $workID = @_ ? shift : $lastWorkID;
	my $tokenID = shift || $workContext{$workID}->{tokenID};
	$updateRefEndToken->execute($tokenID, $workContext{$workID}->{bookRefID})    if $workContext{$workID}->{bookRefID};
	$updateRefEndToken->execute($tokenID, $workContext{$workID}->{chapterRefID}) if $workContext{$workID}->{chapterRefID};
	$updateRefEndToken->execute($tokenID, $workContext{$workID}->{verseRefID})   if $workContext{$workID}->{verseRefID};
	#$workContext{$workID}->{refPosition} = 1; #reset for each book
}


sub parseUnboundVerseLine {
	my $d = shift;
	my $varNum = shift;

	#Keep only the requested variant
	if($varNum){
		$d =~ s/{VAR$varNum:(.+?)}/$1/g;
		$d =~ s/{VAR(?!$varNum)\d+:(.+?)}//g;
	}
	
	#Trim
	$d =~ s/^\s+//;
	$d =~ s/\s+$//;
	
	$d =~ s/
		(\[(?!\s)\b\p{Greek}+.+?\p{Greek}\b(?<!\s)\]) #This needs to be revised: no Greek
	 /
		addVariantBrackets($1)
	 /gxe;
	
	#Parse multiple words on a line
	$d = NFC($d);
	my @results = $d =~ m/
		\s*(\[?\p{Greek}+\]?)   #This needs to be revised: no Greek
		\s+(G0*\d+)
		\s+([A-Z\ 0-9\-]+?)
		(?=\s+\[?\p{Greek}|\s*$|\s*{)   #This needs to be revised: no Greek
	 /gx;
	
	my @tokens;
	for(my $i = 0; $i < @results; $i += 3){
		my $data = $results[$i];
		my $type = 'word';
		my $parsing = $results[$i+1] . ' ' . $results[$i+2];
		my @strongs = $parsing =~ m/([GH]\d+)/g;
		$parsing =~ s{G\d+}{}g;
		$parsing =~ s/^\s+//;
		
		push @tokens, {
			data       => $data,
			type       => $type,
			strongs    => \@strongs,
			#lemma      => $strongsDict{$strongs}->{word},
			#kjv_def    => $strongsDict{$strongs}->{kjv_def},
			#strongs_def => $strongsDict{$strongs}->{strongs_def},
			parse      => $parsing
		};
	}
	return @tokens;
}

sub addVariantBrackets {
	my $s = shift;
	
	$s =~ s/
		(?<!\[)(\b\p{Greek}+)
	 /\[$1/gx;
	
	$s =~ s/
		(\p{Greek}+\b)(?!\])
	 /$1\]/gx;
	
	return $s;
}



sub normalizeToken {
	my $word = shift;
	$word = lc $word;
	$word = decompose($word);
	$word =~ s/  ' | ’  |  \p{IsDiacritic} | \[ | \] //gx;
	return NFC($word);
}

1;
